Oracle Quotes

Mr. Muskrat on 2007-08-24T20:57:42

Oracle throws some odd errors if you mistakenly use the wrong kind of quotes.

SQL> CREATE OR REPLACE FUNCTION test (p_in IN VARCHAR2)
  2  RETURN NUMBER
  3  AS
  4  BEGIN
  5    IF p_in = "" THEN
  6      RETURN 0;
  7    END IF;
  8  END test;
  9  /
CREATE OR REPLACE FUNCTION test (p_in IN VARCHAR2)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME")

Say what? That error message does anything but scream "you used the wrong quoting character".

SQL> CREATE OR REPLACE FUNCTION test (p_in IN VARCHAR2)
  2  RETURN NUMBER
  3  AS
  4  BEGIN
  5    IF p_in = '' THEN
  6      RETURN 0;
  7    END IF;
  8  END test;
  9  /

Function created.

How hard is it...

Mr. Muskrat on 2007-08-24T21:08:02

... to add an appropriate error message?

ERROR at line 5: ORA-99999: wrong quoting character used

or even

ERROR at line 5: ORA-ID10T: Use single quotes instead dummy!

Re:How hard is it...

runrig on 2007-08-24T23:44:45
Or to allow EITHER single or double quoting (like some databases *cough* Informix *cough* allow)? I was spoiled by learning SQL on Informix, so when I started on Oracle and MS-SQL, I cursed and then learned to change my habits. I suppose there's some sort of ANSI standard though...

Re:How hard is it...

Mr. Muskrat on 2007-08-25T15:15:21
Exactly!
Re:How hard is it...

Alias on 2007-08-26T01:54:54
I guess the problem with that is they have semantically different meanings.

One looks to be a constructor for literals, the other is a quote for database object names.

Also...

bart on 2007-08-24T22:54:09

There's no such thing as a zero length string in Oracle. '' is identical to NULL. So

p_in = ''

will never return true.

Use

p_in is null

instead.

Re:Also...

Mr. Muskrat on 2007-08-25T15:14:18
You'd think that after using Oracle for a year and a half that I would remember that and yet I keep forgetting.

Re:Also...

sigzero on 2007-08-25T20:04:34
No, we all forget that sometimes.